package org.geoscheduler.android.tools;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.List;

import org.geoscheduler.model.Identifiable;
import org.geoscheduler.model.Tag;
import org.geoscheduler.model.TagEvent;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public final class DBHelper {
	/** Name of the Event DB, unique within application package */
	public static final String EVENTS_DB_NAME = "EVENTS_DB";	

	/** Current version of the Event DB */
	public static final int EVENTS_DB_VERSION = 1;
	
	/** DDL script path */
	public static final String DDL_PATH = "gs-client-schema.sql";
	
	public static SQLiteDatabase openDB(Context ctx) {
		return ctx.openOrCreateDatabase(EVENTS_DB_NAME, Context.MODE_PRIVATE, null);
	}
	
	public static void deleteDB(Context ctx) {
		ctx.deleteDatabase(EVENTS_DB_NAME);
	}
	
	public static void createDB(Context ctx, LogCallback log) throws IOException {
		SQLiteDatabase ret = ctx.openOrCreateDatabase(EVENTS_DB_NAME, Context.MODE_PRIVATE, null);
		
		/* Read SQLs from DDL script and after each semicolon execute */
		InputStream inp = ctx.getAssets().open(DDL_PATH);
		BufferedReader reader = new BufferedReader(new InputStreamReader(inp));
		
		StringBuffer sqlText = new StringBuffer();
		
		while (reader.ready()) {
			String textLine = reader.readLine();
			
			/* Comments are skipped */
			if (textLine.startsWith("-")) {
				continue;
			}
			
			sqlText.append(textLine);
			sqlText.append(" ");
			
			if (textLine.indexOf(';') > -1) {
				String sqlTextStr = sqlText.toString();
				
				/* Writing first line into log */
				if (log != null) {
					int i = sqlTextStr.indexOf("(");
					if (i > -1) {
						log.writeLog(sqlTextStr.substring(0, i).trim());
					} else {
						log.writeLog(sqlTextStr.trim());
					}
				}

				/* Execute SQL */
				ret.execSQL(sqlTextStr);
				
				/* Clean buffer */
				sqlText = new StringBuffer();
			}
		}
	}
	
	
	/*
	 * VARIOUS QUERIES 
	 */
	
	public static boolean fetchInst(SQLiteDatabase dbInst, long id, Identifiable target) {
		Cursor cr = dbInst.query(DBModel.getTableName(target), 
				DBModel.getColumnNames(target), 
				DBModel.getIDField(target) + "=" + id, null, null, null, null);
		
		if (cr.getCount() > 0) {
			cr.moveToFirst();
			
			DBModel.fillContentValues(cr, target);
		
			return true;
		} else {
			return false;
		}
	}
	
	public static List<Identifiable> fetchAllInsts(SQLiteDatabase dbInst, Identifiable firstTarget) {
		Cursor cr = dbInst.query(DBModel.getTableName(firstTarget), 
				DBModel.getColumnNames(firstTarget), 
				null, null, null, null, null);
		
		List<Identifiable> ret = new ArrayList<Identifiable>(); 
		if (cr.getCount() > 0) {
			cr.moveToFirst();
			
			do {
				Identifiable item;
				if (cr.isFirst()) {
					item = firstTarget;
				} else {
					item = DBModel.getFreshInstance(firstTarget);
				}				
				DBModel.fillContentValues(cr, item);
				ret.add(item);
			} while (cr.moveToNext());
		}
		
		return ret;
	}
	
	public static List<Tag> fetchTags(SQLiteDatabase dbInst, Long idEvent) {
		List<Tag> ret = new ArrayList<Tag>();
		
		Cursor cr = dbInst.query(DBModel.TagEventCols.TABLE_NAME, 
				new String[] { DBModel.TagEventCols.ID_TAG }, 
				DBModel.TagEventCols.ID_EVENT + "=" + idEvent, null, null, null, null);
		
		if (cr.getCount() > 0) {
			cr.moveToFirst();
		
			do {				
				Tag tag = new Tag();
				if (DBHelper.fetchInst(dbInst, cr.getLong(0), tag)) {
					ret.add(tag);
				}				
			} while (cr.moveToNext());
		}
		
		return ret;
	}
	
	public static long createInst(SQLiteDatabase dbInst, Identifiable inst) {
		String tableName = DBModel.getTableName(inst);
		ContentValues values = DBModel.getContentValues(inst);
		long id = dbInst.insert(tableName, null, values);
		return id;
	}
	
	public static boolean updateInst(SQLiteDatabase dbInst, Identifiable inst) {
		String tableName = DBModel.getTableName(inst);
		ContentValues values = DBModel.getContentValues(inst);
		int affected = dbInst.update(tableName, 
				values, 
				DBModel.getIDField(inst) + "=" + inst.getId(), 
				null);
		return affected == 1;
	}
	
	public static long storeInst(SQLiteDatabase dbInst, Identifiable inst) {
		if (inst.getId() != null) {
			if (DBHelper.updateInst(dbInst, inst)) {
				return inst.getId();
			} else {
				return -1;
			}
		} else {
			return DBHelper.createInst(dbInst, inst);
		}
	}
	
	public static void updateTags(SQLiteDatabase dbInst, List<Tag> tags,Long idEvent) {
		String tableName = DBModel.TagEventCols.TABLE_NAME;
		String whereClause = DBModel.TagEventCols.ID_EVENT + "=" + idEvent; 
		
		dbInst.delete(tableName, whereClause, null);
		
		for (Tag tag: tags) {
			DBHelper.storeInst(dbInst, tag);
			tag = DBHelper.fetchTag(dbInst, tag.getName());
			TagEvent tagEvent = new TagEvent(idEvent, tag.getId());
						
			ContentValues values = DBModel.getContentValues(tagEvent);
			dbInst.insert(tableName, null, values);
		}
	}
	
	public static Tag fetchTag(SQLiteDatabase dbInst, String name) {
		Tag ret = new Tag();
		
		Cursor cr = dbInst.query(DBModel.getTableName(ret), 
				DBModel.getColumnNames(ret), 
				DBModel.TagCols.NAME + "='" + name + "'", null, null, null, null);
		
		if (cr.getCount() > 0) {
			cr.moveToFirst();
			
			DBModel.fillContentValues(cr, ret);
		
			return ret;
		} else {
			return null;
		}
	}
	
	public static boolean deleteInst(SQLiteDatabase dbInst, Identifiable inst) {
		String tableName = DBModel.getTableName(inst);
		String whereClause = DBModel.getIDField(inst) + "=" + inst.getId(); 
		
		int affected = dbInst.delete(tableName, whereClause, null);
		
		return affected == 1;
	}
	
	public static Identifiable fetchOne(SQLiteDatabase dbInst, Identifiable inst, String whereClause) {
		Cursor cr = dbInst.query(
				DBModel.getTableName(inst), 
				DBModel.getColumnNames(inst), 
				whereClause, 
				null, null, null, null);
		
		if (cr.getCount() > 0) {
			cr.moveToFirst();
			
			DBModel.fillContentValues(cr, inst);
			return inst;
		} else {
			return null;
		}
	}
}
